SET NOCOUNT ON
GO

IF (SELECT OBJECT_ID('up_GenerateSLTrigger','P')) IS NOT NULL --means, the procedure already exists
		DROP PROC up_GenerateSLTrigger
GO

--!!! procedure was tested only with @TableName parameter. Leave all by default
CREATE PROC up_GenerateSLTrigger
(
	@TableName sysname,  			-- The audited table for which logging trigger will be generated
	@LogTable sysname = 'xSLog', 		-- Use this parameter to specify a name of the log storage table
	@LogTypeName sysname = 'xSLTypeDefHelper',-- Use this parameter to specify a name of the types definitions table that will be used for trigger code generation
	@TriggerPrefix sysname = 'xSLTrig',
	@DoDebug bit = 0,			-- If @DoDebug is set to 1, the SQL statements constructed by this procedure will be printed for later examination
	@TableOwner sysname = NULL,		-- Use this parameter to (1) include Owner qualifier in generated statements (2) generate trigger for table not owned by current user
	@TableDB sysname = NULL,		-- Use this parameter to include Database qualifier in generated statements (it must be the same as current database)
	@LogOwner sysname = NULL,		-- Use this parameter to include Owner qualifier when accessing log table in generated trigger
	@LogDB sysname = NULL,			-- Use this parameter to include database qualifier when accessing log table in generated trigger

	@IncludeTimesatampData bit = 0, 	-- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT/UPDATE statements generated by trigger.
						-- Statements will not be executable in this case
	@IncludeBLOBData bit = 1, 		-- Specify 1 for this parameter, if you want to include the TEXT/NTEXT/IMAGE column's data in the statements generated by trigger
	@IncludeIdentityData bit = 1, 		-- Specify 1 for this parameter, if you want to include the "identity" columns and values in the INSERT statements generated by trigger
	@IncludeComputedData bit = 0,		-- When 1, computed columns will not be included in the INSERT statement
						--(VB?) have to test it
	@DoLogBitStmnt varchar(1000) = '1'	--statement that returns 0/1, if 0 no logging will be done but simly return from trigger --(VB-2)


)
AS
BEGIN

/***********************************************************************************************************
Procedure:	up_GenerateSLTrigger (Build 2) 
--(VB-2)	All variables for trigger code storage are substituted with inserts into #ltrig 
                                          
Purpose:	To generate LOGGING TRIGGER DDL statement for audited table.
		This LOGGING TRIGGER will generate DML statements depending on contents of "inserted" and "deleted" tables
		and store them in @LogDB.@LogOwner.@LogTable table that must has same columns as xSLog.

Tested on: 	SQL Server 7.0 and SQL Server 2000

Date created:	August 24th 2002

Date modified:	August 25th 2002

NOTE:		Generated triggers may not work with tables with too many and large columns (when log record will exceed 8060 bytes).
		Dummy insert statements will not contain BLOB data as it is not presented in the "deleted" table

Main workflow:
		Declare and initialize local tables and variables (differentiate from external environment)
		Perform some checks
		Create Trigger statement header and comments
		Check parameters passed
		.....................
		(description is unfinished)

Date modified:	March 2006
		appended collate database_default in couple of places

***********************************************************************************************************/

SET NOCOUNT ON

--************************** Initialize local variables (differentiate from external environment)

declare @cr varchar(2), @tab varchar (1) 
--names of log tables 
declare 
	@table_owner sysname, 	--audit table owner name
	@table_db sysname, 	--audit table db name
	@fq_log_table nvarchar(392), --fully qualified log table name (sysname * 3 + 2)
	@fq_table nvarchar(392), --fully qualified audited table name (sysname * 3 + 2)

	@oldcur_name sysname, @newcur_name sysname,
	@join_newPK nvarchar(4000),
	@last_id int	--(VB-2)

--	@StmntDecl nvarchar (4000),		-- generated script
--	@StmntBody nvarchar (4000),		-- generated script
--	@StmntCycleStart nvarchar (4000),		-- generated script
--	@StmntCycleCommon nvarchar (4000),		-- generated script
--	@StmntCycleCommonText nvarchar (4000),		-- generated script
--	@StmntCycleInsert nvarchar (4000),		-- generated script
--	@StmntCycleInsertOld nvarchar (4000),		-- generated script
--	@StmntCycleUpdate varchar (8000),		-- generated script
--	@StmntCycleDelete varchar (8000),		-- generated script
--	@StmntCycleEnd varchar (8000)		-- generated script
	
create table #ltrig (id int identity, row nvarchar(4000))	--(VB-2)

select @cr = CHAR(13) + CHAR(10)		--carriage return
select @tab = CHAR(9)				--TAB

select @table_owner = ISNULL(@TableOwner, user_name())
select @table_db = ISNULL(@TableDB, DB_NAME())
select @fq_table = ISNULL(quotename(@TableDB), '') + ISNULL(substring(@TableDB, 1, 0) + '.', '') 		--table db name qualifier
		+ ISNULL(quotename(@TableOwner), '') + ISNULL(substring(@TableOwner, 1, 0) + '.', '')		--table owner qualifier
		+ quotename(@TableName) 									--audited table name

select @fq_log_table = ISNULL(quotename(@LogDB), '') + ISNULL(substring(@LogDB, 1, 0) + '.', '') 	--log db name qualifier
		+ ISNULL(quotename(@LogOwner), '') + ISNULL(substring(@LogOwner, 1, 0) + '.', '') 	--log owner qualifier
		+ quotename(@LogTable) 									--log table name

--************************** Perform some checks

--Checking that all necessary types are described
declare @tmp_missing_datatype sysname
select @tmp_missing_datatype = DATA_TYPE 
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_CATALOG = @table_db 
and TABLE_SCHEMA = @table_owner
and TABLE_NAME = @TableName 
and not exists (select 1 from xSLTypeHelper where TypeName = DATA_TYPE collate database_default)

if (@tmp_missing_datatype is not null) begin
	select @tmp_missing_datatype = 'The data type ' + @tmp_missing_datatype + ' is not defined in xSLTypeHelper.'
	RAISERROR(@tmp_missing_datatype,16,1)
	RETURN -1 --Failure. Reason: xSLTypeHelper does not contain all data types used by the table
end

--Checking that current database is the same as specified for the existence of 'user table'
if (@TableDB !=  DB_NAME()) begin
		RAISERROR('Current database differs from specified in @TableDB parameter.',16,1)
		PRINT 'In order to confirm SQL-92 Standard as much as possible, INFORMATION_SCHEMA views are used to access system tables data.'
		PRINT 'INFORMATION_SCHEMA views have database level scope.'
		RETURN -1 --Failure. Reason: We cannot switch to another database at execution time
	END

--Checking for the existence of 'user table'
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES 
		WHERE TABLE_NAME = @TableName AND (TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = @table_owner))	BEGIN
			RAISERROR('User table %s.%s not found.',16,1, @table_owner, @TableName )
			PRINT 'You may see this error, if you are not the owner of this table. In that case use @owner parameter to specify the owner name.'
			PRINT 'Make sure you have SELECT permission on that table.'
			RETURN -1 --Failure. Reason: There is no table with this name
		END

--************************** Initialize local helper table (differentiate from external environment)
select COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, NUMERIC_PRECISION, NUMERIC_SCALE, CHARACTER_MAXIMUM_LENGTH,
xSLTypeHelper.*, 
convert(bit, 0) as IsIdentity, convert(bit, 0) as IsComputed, convert(bit, 0) as IsPK,
convert(nvarchar(200), 0) as DeclStmnt 
into #col_helper 
from INFORMATION_SCHEMA.COLUMNS LEFT OUTER JOIN xSLTypeHelper 	--correct this row if another type helper table is used
on DATA_TYPE = TypeName collate database_default
where TABLE_CATALOG = @table_db
and TABLE_SCHEMA = @table_owner
and TABLE_NAME = @TableName
and (IsBLOB = 0 or @IncludeBLOBData = 1)

--add and set PK, identity and computed flags
update #col_helper 
set IsIdentity =  COLUMNPROPERTY( OBJECT_ID(QUOTENAME(@table_owner) + '.' + QUOTENAME(@TableName)),COLUMN_NAME,'IsIdentity'),
IsComputed = COLUMNPROPERTY( OBJECT_ID(QUOTENAME(@table_owner) + '.' + QUOTENAME(@TableName)),COLUMN_NAME,'IsComputed'),
IsPK = isnull( (select 1 
		from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
			on UPPER(ccu.TABLE_CATALOG) = UPPER(tc.TABLE_CATALOG)
			and UPPER(ccu.TABLE_SCHEMA) = UPPER(tc.TABLE_SCHEMA)
			and UPPER(ccu.TABLE_NAME) = UPPER(tc.TABLE_NAME)
			and UPPER(ccu.CONSTRAINT_CATALOG) = UPPER(tc.CONSTRAINT_CATALOG)
			and UPPER(ccu.CONSTRAINT_SCHEMA) = UPPER(tc.CONSTRAINT_SCHEMA)
			and UPPER(ccu.CONSTRAINT_NAME) = UPPER(tc.CONSTRAINT_NAME)
		where tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
		and UPPER(tc.TABLE_CATALOG) = UPPER(@table_db)
		and UPPER(tc.TABLE_SCHEMA) = UPPER(@table_owner)
		and UPPER(tc.TABLE_NAME) = UPPER(@TableName)
		and UPPER(ccu.COLUMN_NAME) = UPPER(#col_helper.COLUMN_NAME)), 0)

--Checking for the existence of PK in 'user table'
IF NOT EXISTS (SELECT 1 FROM #col_helper WHERE IsPK = 1)BEGIN
			RAISERROR('User table %s.%s does not have primary key fefined.',16,1, @table_owner, @TableName )
			RETURN -1 --Failure. Reason: There is no table with this name
		END

--add and set variable declaration statements for each column
update #col_helper 
set DeclStmnt = DATA_TYPE
where HasPrec = 0 and HasScale = 0 and HasLength = 0 
update #col_helper 
set DeclStmnt = DATA_TYPE + isnull('(' + cast(NUMERIC_PRECISION as varchar(10)) + ')', '')
where HasPrec = 1 and HasScale = 0 and HasLength = 0 
update #col_helper 
set DeclStmnt = DATA_TYPE + isnull('(' + cast(NUMERIC_PRECISION as varchar(10)) + isnull(',' + cast(NUMERIC_SCALE as varchar(10)) + ')', ')'), '')
where HasPrec = 1 and HasScale = 1 and HasLength = 0 
update #col_helper 
set DeclStmnt = DATA_TYPE + '(' + cast(CHARACTER_MAXIMUM_LENGTH as varchar(10)) +  ')'
where HasPrec = 0 and HasScale = 0 and HasLength = 1 

update #col_helper 
set DeclStmnt = DATA_TYPE + isnull('(' + cast(NUMERIC_PRECISION as varchar(10)) + ')', '')
where HasPrec = 1 and HasScale = 0
update #col_helper 
set DeclStmnt = DATA_TYPE + isnull('(' + cast(NUMERIC_PRECISION as varchar(10)) + isnull(',' + cast(NUMERIC_SCALE as varchar(10)) + ')', ')'), '')
where HasPrec = 1 and HasScale = 1


if (@DoDebug = 1) begin
	PRINT '/*****BEGIN DEBUG INFORMATION - Contents of #col_helper *****'
	select * from #col_helper
	PRINT '/*****END DEBUG INFORMATION - Contents of #col_helper *****'
end

--************************** Generate trigger header
insert into #ltrig (row) select 'DROP TRIGGER [' + @TriggerPrefix + '_' + @TableName + ']'+ @cr
insert into #ltrig (row) select 'go'+ @cr
insert into #ltrig (row) select 'CREATE TRIGGER [' + @TriggerPrefix + '_' + @TableName + '] ON '+ @fq_table +' '+ @cr
insert into #ltrig (row) select 'FOR INSERT, UPDATE, DELETE '+ @cr
insert into #ltrig (row) select 'AS BEGIN'+ @cr
insert into #ltrig (row) select '/*                                                         */'+ @cr
insert into #ltrig (row) select '/*     Trigger of SLog                                     */'+ @cr
insert into #ltrig (row) select '/*     Generated by procedure up_GenerateSLTrigger         */'+ @cr
insert into #ltrig (row) select '/*                                                         */'+ @cr + @cr
--************************** Generate variable declarations

insert into #ltrig (row) select 'declare @action char(1), ' + @cr
insert into #ltrig (row) select @tab + '@old_count int, @new_count int, @numrows int,' + @cr
insert into #ltrig (row) select @tab + '@pk_old_values nvarchar(4000), @pk_new_values nvarchar(4000), @pk_names nvarchar(4000), '+ @cr
insert into #ltrig (row) select @tab + '@sql_flist nvarchar(4000), @sql_vlist nvarchar(4000), @sql_fset_list nvarchar(4000), '+ @cr
insert into #ltrig (row) select @tab + '@sql_where_list nvarchar(4000), '
insert into #ltrig (row) select @tab + '@sql_blob_flist nvarchar(4000), @sql_blob_vlist nvarchar(4000), @sql_blob_fset_list nvarchar(4000),'+ @cr
insert into #ltrig (row) select @tab + '@sql_blob_from_list nvarchar(4000), @sql_blob_where_list nvarchar(4000), '

insert into #ltrig (row) select @tab + '@has_valid_blob bit, @stmnt nvarchar(4000), '+ @cr

insert into #ltrig (row) select @tab + 	'@new_' + COLUMN_NAME + ' ' + DeclStmnt + ', ' +  
					'@old_' + COLUMN_NAME + ' ' + DeclStmnt + ',' + @cr
			from #col_helper where IsBLOB = 0 order by ORDINAL_POSITION
if (@IncludeBLOBData = 1) 
	insert into #ltrig (row) 
	select 	@tab + '@ptr_' + COLUMN_NAME + ' varbinary(15), ' +  '@ident_' + COLUMN_NAME + ' int,' + @cr
	from #col_helper where IsBLOB = 1 order by ORDINAL_POSITION
select @last_id = @@IDENTITY
update #ltrig set row = left(row, len(row) - 1 - len(@cr)) + @cr where id = @last_id

if (@DoDebug = 1) begin
	PRINT '/*****BEGIN DEBUG INFORMATION - Trigger declarations *****'
	select * from #ltrig
	PRINT '/*****END DEBUG INFORMATION - Trigger declarations *****'
end

--************************** Generate trigger body
--determine DML statement type
insert into #ltrig (row) select 'select @numrows = @@rowcount' + @cr
insert into #ltrig (row) select 'if (@numrows = 0) return' + @cr
insert into #ltrig (row) select 'if (isnull((' + @DoLogBitStmnt + '),1) = 0) return' + @cr		--(VB-2)
insert into #ltrig (row) select 'set nocount on '+ @cr
insert into #ltrig (row) select 'select @old_count = count(*) from deleted' + @cr
insert into #ltrig (row) select 'select @new_count = count(*) from inserted'+ @cr
insert into #ltrig (row) select 'IF (@new_count<>0)'+ @cr
insert into #ltrig (row) select '  IF (@old_count<>0) SELECT @action = ''U'''+ @cr
insert into #ltrig (row) select '  ELSE SELECT @action = ''I'''+ @cr
insert into #ltrig (row) select 'ELSE'+ @cr
insert into #ltrig (row) select '  IF (@old_count<>0) SELECT @action = ''D'''+ @cr
insert into #ltrig (row) select '  ELSE SELECT @action = ''N'''+ @cr

--generate declarations for inserted and deleted cursors
select @oldcur_name = @TriggerPrefix + '_' + @TableName + '_OldCur'
insert into #ltrig (row) select 'DECLARE ' + @oldcur_name + ' INSENSITIVE CURSOR FOR SELECT'+ @cr
insert into #ltrig (row) select COLUMN_NAME + ',' + @cr from #col_helper where IsBLOB = 0 order by ORDINAL_POSITION
select @last_id = @@IDENTITY
update #ltrig set row = left(row, len(row) - 1 - len(@cr))+ @cr  where id = @last_id --eleminate last ",CR"
insert into #ltrig (row) select 'FROM deleted FOR READ ONLY'+ @cr

select @newcur_name = @TriggerPrefix + '_' + @TableName + '_NewCur'
insert into #ltrig (row) select 'DECLARE ' + @newcur_name + ' INSENSITIVE CURSOR FOR SELECT'+ @cr
insert into #ltrig (row) select COLUMN_NAME + ','  + @cr from #col_helper where IsBLOB = 0 order by ORDINAL_POSITION
select @last_id = @@IDENTITY
update #ltrig set row = left(row, len(row) - 1 - len(@cr))+ @cr  where id = @last_id --eleminate last ",CR"
insert into #ltrig (row) select 'FROM inserted FOR READ ONLY'+ @cr

--generate open statments for inserted and deleted cursors
insert into #ltrig (row) select 'IF (@old_count <> 0) OPEN ' + @oldcur_name + @cr
insert into #ltrig (row) select 'IF (@new_count <> 0) OPEN ' + @newcur_name + @cr

if (@DoDebug = 1) begin
	PRINT '/*****BEGIN DEBUG INFORMATION - Trigger body before cycle *****'
	select * from #ltrig
	PRINT '/*****END DEBUG INFORMATION - Trigger body before cycle *****'
end

--generate cursor cycle enter
insert into #ltrig (row) select 'WHILE (1=1) begin' + @cr

--generate fetch statements
insert into #ltrig (row) select 'IF (@old_count <> 0) FETCH NEXT FROM ' + @oldcur_name + ' INTO ' + @cr
insert into #ltrig (row) select '@old_' + COLUMN_NAME + ',' + @cr from #col_helper where IsBLOB = 0 order by ORDINAL_POSITION
select @last_id = @@IDENTITY
update #ltrig set row = left(row, len(row) - 1 - len(@cr)) + @cr where id = @last_id --eleminate last ",CR"

insert into #ltrig (row) select 'IF (@new_count <> 0) FETCH NEXT FROM ' + @newcur_name + ' INTO ' + @cr
insert into #ltrig (row) select '@new_' + COLUMN_NAME + ',' + @cr from #col_helper where IsBLOB = 0 order by ORDINAL_POSITION
select @last_id = @@IDENTITY
update #ltrig set row = left(row, len(row) - 1 - len(@cr)) + @cr where id = @last_id --eleminate last ",CR"

--generate fetch status check/cycle break
insert into #ltrig (row) select 'IF (@@FETCH_STATUS = -1) BREAK' + @cr 

if (@DoDebug = 1) begin
	PRINT '/*****BEGIN DEBUG INFORMATION - Trigger cycle start *****'
	select * from #ltrig
	PRINT '/*****END DEBUG INFORMATION - Trigger cycle start *****'
end

select @join_newPK = ''
select @join_newPK = @join_newPK + COLUMN_NAME + ' = ' + '@new_' + COLUMN_NAME + ' and '
from #col_helper where IsPK = 1 order by ORDINAL_POSITION
select @join_newPK = left(@join_newPK, len(@join_newPK) - len(' and ')) --eleminate last " and "
if (@IncludeBLOBData = 1) begin
--generate pointers to BLOB data extraction
	insert into #ltrig (row) select 'SELECT @ptr_' + COLUMN_NAME + '= TEXTPTR(' + COLUMN_NAME + ') FROM ' + @TableName + 
					' WHERE ' + @join_newPK + @cr
				from #col_helper where IsBLOB = 1 order by ORDINAL_POSITION
end
insert into #ltrig (row) select @cr

--generate PK names and values extraction
declare @sel_pks nvarchar(4000)
select @sel_pks = 'select @pk_names = '''
select @sel_pks = @sel_pks + QUOTENAME(COLUMN_NAME) + ';' from #col_helper where IsPK = 1 order by ORDINAL_POSITION
select @sel_pks = @sel_pks + '''' + @cr
insert into #ltrig (row) select @sel_pks

select @sel_pks = 'select @pk_new_values = '
select @sel_pks = @sel_pks + CharConvPref + '@new_' + COLUMN_NAME + CharConvPost + '+'';''+' 
from #col_helper where IsPK = 1 order by ORDINAL_POSITION
select @sel_pks = left(@sel_pks, len(@sel_pks) - 1) + @cr --eleminate last "+"
insert into #ltrig (row) select @sel_pks

select @sel_pks =  'select @pk_old_values = '
select @sel_pks = @sel_pks + CharConvPref + '@old_' + COLUMN_NAME + CharConvPost + '+'';''+' 
from #col_helper where IsPK = 1 order by ORDINAL_POSITION
select @sel_pks = left(@sel_pks, len(@sel_pks) - 1) + @cr --eleminate last "+"
insert into #ltrig (row) select @sel_pks

if (@DoDebug = 1) begin
	PRINT '/*****BEGIN DEBUG INFORMATION - Trigger cycle before statements *****'
	select * from #ltrig
	PRINT '/*****END DEBUG INFORMATION - Trigger cycle before statements*****'
end

insert into #ltrig (row) select 'select @has_valid_blob = 0' + @cr
if (@IncludeBLOBData = 1) begin
--generate INSERT and UPDATE statement parts for BLOB data
        insert into #ltrig (row) select 'select @sql_blob_flist = ''''' + @cr
        insert into #ltrig (row) select 'select @sql_blob_vlist = ''''' + @cr
        insert into #ltrig (row) select 'select @sql_blob_fset_list = ''''' + @cr
        insert into #ltrig (row) select 'select @sql_blob_from_list = ''''' + @cr
        insert into #ltrig (row) select 'select @sql_blob_where_list = ''''' + @cr

	insert into #ltrig (row) select 
	'if (TEXTVALID(''' + @fq_table + '.' + COLUMN_NAME + ''', @ptr_' + COLUMN_NAME + ') = 1) begin' + @cr +
		@tab + 'insert into ' + BLOBTable + '(Value) select ' + COLUMN_NAME + ' from ' + @fq_table +  @cr + 
		@tab + 'where ' + @join_newPK + @cr + 
		@tab + 'select @ident_' + COLUMN_NAME + '= @@IDENTITY' + @cr + 
		@tab + 'select @sql_blob_flist = @sql_blob_flist + ''' + COLUMN_NAME + ',''' + @cr +
		@tab + 'select @sql_blob_vlist = @sql_blob_vlist + ''' + BLOBTable + ''' + convert(varchar(20), @ident_' + COLUMN_NAME + ') + ''.Value,''' + @cr +
		@tab + 'select @sql_blob_fset_list = @sql_blob_fset_list + ''' + COLUMN_NAME + ' = ' + BLOBTable + ''' + convert(varchar(20), @ident_' + COLUMN_NAME + ') + ''.Value,''' + @cr +
	        @tab + 'select @sql_blob_from_list = @sql_blob_from_list + ''' + BLOBTable + ' ' + BLOBTable + ''' + convert(varchar(20), @ident_' + COLUMN_NAME + ') + '',''' + @cr +
		@tab + 'select @sql_blob_where_list = @sql_blob_where_list + '' ' + BLOBTable + ''' + convert(varchar(20), @ident_' + COLUMN_NAME + ') + ''.ID = '' + convert(varchar(20), @ident_' + COLUMN_NAME + ') + '' AND''' + @cr +
		@tab + 'select @has_valid_blob = 1' + @cr +
          'end ' + @cr 
	from #col_helper where IsBLOB = 1 order by ORDINAL_POSITION

	insert into #ltrig (row) select 'if (@has_valid_blob = 1) begin' + @cr
	insert into #ltrig (row) select @tab + 'select @sql_blob_flist = left(@sql_blob_flist, len(@sql_blob_flist) - 1 ) --elim last ","'+ @cr 
	insert into #ltrig (row) select @tab + 'select @sql_blob_vlist = left(@sql_blob_vlist, len(@sql_blob_vlist) - 1 ) --elim last ","'+ @cr 
	insert into #ltrig (row) select @tab + 'select @sql_blob_fset_list = left(@sql_blob_fset_list, len(@sql_blob_fset_list) - 1 ) --elim last ","'+ @cr 
	insert into #ltrig (row) select @tab + 'select @sql_blob_from_list = left(@sql_blob_from_list, len(@sql_blob_from_list) - 1 ) --elim last ","'+ @cr 
	insert into #ltrig (row) select @tab + 'select @sql_blob_where_list = left(@sql_blob_where_list, len(@sql_blob_where_list) - len('' AND'')) --elim last " AND"'+ @cr 
	insert into #ltrig (row) select 'end' + @cr
end

if (@DoDebug = 1) begin
	PRINT '/*****BEGIN DEBUG INFORMATION - Trigger cycle before statements - text *****'
	select * from #ltrig
	PRINT '/*****END DEBUG INFORMATION - Trigger cycle before statements - text *****'
end
--generate statements 
insert into #ltrig (row) select 'if ((@action = ''I'') '    
insert into #ltrig (row) select '	or not exists (select 1 from ' + @fq_log_table + @cr
insert into #ltrig (row) select @tab + '                   where TableName = ''' + @fq_table + '''' + @cr
insert into #ltrig (row) select @tab + '                     and PKFields = @pk_names' + @cr
insert into #ltrig (row) select @tab + '                     and NewPKValues = @pk_old_values)' + @cr
insert into #ltrig (row) select @tab + '   ) begin' + @cr
insert into #ltrig (row) select '     if (@action = ''I'') begin' + @cr


--generate INSERT statement parts for regular data
insert into #ltrig (row) select @tab + 'select @sql_flist = ''''' + @cr
insert into #ltrig (row) select @tab + 'select @sql_vlist = ''''' + @cr


insert into #ltrig (row) select 
	@tab + 'if (@new_' + COLUMN_NAME + ' is not null) begin' + @cr +
	@tab + @tab + 'select @sql_flist = @sql_flist + ''' + COLUMN_NAME + ',''' + @cr +
	@tab + @tab + 'select @sql_vlist = @sql_vlist + ' + CharConvPref + '@new_' + COLUMN_NAME + CharConvPost + ' + '',''' + @cr + 
	@tab + 'end ' + @cr 
from #col_helper where IsBLOB = 0 order by ORDINAL_POSITION

insert into #ltrig (row) select @tab + 'select @sql_flist = left(@sql_flist, len(@sql_flist) - 1 ) --elim last ","'+ @cr 
insert into #ltrig (row) select @tab + 'select @sql_vlist = left(@sql_vlist, len(@sql_vlist) - 1 ) --elim last ","'+ @cr 

insert into #ltrig (row) select @tab + 'if (@has_valid_blob = 1) begin' + @cr
insert into #ltrig (row) select @tab + @tab + 'select @sql_flist = @sql_flist + '','' + @sql_blob_flist ' + @cr
insert into #ltrig (row) select @tab + @tab + 'select @sql_vlist = @sql_vlist + '','' + @sql_blob_vlist ' + @cr
insert into #ltrig (row) select @tab + 'end' + @cr

insert into #ltrig (row) select @tab + 'select @stmnt = ''INSERT INTO ' + @fq_table + '('' + @sql_flist + '') ''' + @cr
insert into #ltrig (row) select @tab + 'select @stmnt = @stmnt + ''SELECT '' + @sql_vlist + '' ''' + @cr
insert into #ltrig (row) select @tab + 'if (@has_valid_blob = 1) begin' + @cr
insert into #ltrig (row) select @tab + @tab + 'select @stmnt = @stmnt + ''FROM '' + @sql_blob_from_list + '' ''' + @cr
insert into #ltrig (row) select @tab + @tab + 'select @stmnt = @stmnt + ''WHERE '' + @sql_blob_where_list + '' ''' + @cr
insert into #ltrig (row) select @tab + 'end' + @cr
insert into #ltrig (row) select '     end --if (@action = ''I'')' + @cr

----------------------------------------------------------------------------------------------------------------
--generate INSERT statement parts for old data (without BLOBs - they are unaccessible though inserted/deleted
insert into #ltrig (row) select '     else begin ' + @cr

--generate INSERT statement parts for regular data
insert into #ltrig (row) select @tab + 'select @sql_flist = ''''' + @cr
insert into #ltrig (row) select @tab + 'select @sql_vlist = ''''' + @cr

insert into #ltrig (row) select 
	@tab + 'if (@old_' + COLUMN_NAME + ' is not null) begin' + @cr +
	@tab + @tab + 'select @sql_flist = @sql_flist + ''' + COLUMN_NAME + ',''' + @cr +
	@tab + @tab + 'select @sql_vlist = @sql_vlist + ' + CharConvPref + '@old_' + COLUMN_NAME + CharConvPost + ' + '',''' + @cr + 
	@tab + 'end ' + @cr 
from #col_helper where IsBLOB = 0 order by ORDINAL_POSITION

insert into #ltrig (row) select @tab + 'select @sql_flist = left(@sql_flist, len(@sql_flist) - 1 ) --elim last ","'+ @cr 
insert into #ltrig (row) select @tab + 'select @sql_vlist = left(@sql_vlist, len(@sql_vlist) - 1 ) --elim last ","'+ @cr 

insert into #ltrig (row) select @tab + 'select @stmnt = ''INSERT INTO ' + @fq_table + '('' + @sql_flist + '') ''' + @cr
insert into #ltrig (row) select @tab + 'select @stmnt = @stmnt + ''SELECT '' + @sql_vlist + '' ''' + @cr
--generate dummy insert into log table
insert into #ltrig (row) select @tab + 'insert into '+ @LogTable + @cr
insert into #ltrig (row) select @tab + '(Action, Statement, UserName, DateTime, TableName, PKFields, OldPKValues, NewPKValues, SPNestedLevel, TrigNestedLevel, SrvProcessID, IsDummy) ' + @cr
insert into #ltrig (row) select @tab + 'values (''I'', @stmnt, user_name(), getdate(), ''' + @fq_table + ''', @pk_names, null, @pk_old_values, @@NESTLEVEL, trigger_nestlevel(), @@SPID, 1)'+ @cr
insert into #ltrig (row) select '     end --else on if (@action = ''I'')' + @cr
insert into #ltrig (row) select 'end --if (@action = ''I'' or ...)' + @cr

----------------------------------------------------------------------------------------------------------------
insert into #ltrig (row) select 'if (@action = ''U'') begin '+ @cr
insert into #ltrig (row) select @tab + 'select @sql_fset_list = ''''' + @cr

insert into #ltrig (row) select 
	@tab + 'if ((@new_' + COLUMN_NAME + ' != @old_' + COLUMN_NAME + ') or (@new_' + COLUMN_NAME + ' is null and @old_' + COLUMN_NAME + ' is not null) or (@new_' + COLUMN_NAME + ' is not null and @old_' + COLUMN_NAME + ' is null)) begin ' + @cr +
	@tab + @tab + 'select @sql_fset_list = @sql_fset_list + ''' + COLUMN_NAME + ' = '' + ' + CharConvPref + '@new_' + COLUMN_NAME + CharConvPost + ' + '',''' + @cr + 
	@tab + 'end ' + @cr 
from #col_helper where IsBLOB = 0 order by ORDINAL_POSITION

insert into #ltrig (row) select @tab + 'if (len(@sql_fset_list) > 0 ) '
insert into #ltrig (row) select @tab + @tab + 'select @sql_fset_list = left(@sql_fset_list, len(@sql_fset_list) - 1 ) --elim last ","'+ @cr 

insert into #ltrig (row) select @tab + 'if (@has_valid_blob = 1) begin' + @cr
insert into #ltrig (row) select @tab + @tab + 'if (len(@sql_fset_list) > 0 ) select @sql_fset_list = @sql_fset_list + '','' + @sql_blob_fset_list ' + @cr
insert into #ltrig (row) select @tab + @tab + 'else select @sql_fset_list = @sql_blob_fset_list ' + @cr
insert into #ltrig (row) select @tab + 'end' + @cr

insert into #ltrig (row) select @tab + 'select @sql_where_list = ''''' + @cr
insert into #ltrig (row) select @tab + 'select @sql_where_list = @sql_where_list + '' ' + @fq_table + '.' + COLUMN_NAME + ' = '' + ' + CharConvPref + '@old_' + COLUMN_NAME + CharConvPost + ' + '' AND''' + @cr
from #col_helper where IsPK = 1 order by ORDINAL_POSITION
insert into #ltrig (row) select @tab + 'select @sql_where_list = left(@sql_where_list, len(@sql_where_list) - len('' AND'') ) --elim last " AND"'+ @cr 


insert into #ltrig (row) select @tab + 'select @stmnt = ''UPDATE ' + @fq_table + ' SET '' + @sql_fset_list' + @cr
insert into #ltrig (row) select @tab + 'if (@has_valid_blob = 1) begin' + @cr
insert into #ltrig (row) select @tab + @tab + 'select @stmnt = @stmnt + '' FROM '' + @sql_blob_from_list + '' ''' + @cr
insert into #ltrig (row) select @tab + @tab + 'select @stmnt = @stmnt + '' WHERE '' + @sql_blob_where_list + '' ''' + @cr
insert into #ltrig (row) select @tab + @tab + 'select @stmnt = @stmnt + '' AND''' + @cr
insert into #ltrig (row) select @tab + 'end else select @stmnt = @stmnt + '' WHERE ''' + @cr
insert into #ltrig (row) select @tab + 'select @stmnt = @stmnt + '' '' + @sql_where_list' + @cr
insert into #ltrig (row) select '     end --if (@action = ''U'')' + @cr

----------------------------------------------------------------------------------------------------------------
insert into #ltrig (row) select 'if (@action = ''D'') begin '+ @cr

insert into #ltrig (row) select @tab + 'select @sql_where_list = ''''' + @cr
insert into #ltrig (row) select @tab + 'select @sql_where_list = @sql_where_list + '' ' + @fq_table + '.' + COLUMN_NAME + ' = '' + ' + CharConvPref + '@old_' + COLUMN_NAME + CharConvPost + ' + '' AND''' + @cr
			from #col_helper where IsPK = 1 order by ORDINAL_POSITION
insert into #ltrig (row) select @tab + 'select @sql_where_list = left(@sql_where_list, len(@sql_where_list) - len('' AND'') ) --elim last " AND"'+ @cr 

insert into #ltrig (row) select @tab + 'select @stmnt = ''DELETE FROM ' + @fq_table + '''' + @cr
insert into #ltrig (row) select @tab + 'select @stmnt = @stmnt + '' WHERE ''' + @cr
insert into #ltrig (row) select @tab + 'select @stmnt = @stmnt + '' '' + @sql_where_list' + @cr
insert into #ltrig (row) select '     end --if (@action = ''D'')' + @cr

----------------------------------------------------------------------------------------------------------------

--generate insert into log table
insert into #ltrig (row) select @tab + 'insert into '+ @LogTable + @cr
insert into #ltrig (row) select @tab + '(Action, Statement, UserName, DateTime, TableName, PKFields, OldPKValues, NewPKValues, SPNestedLevel, TrigNestedLevel, SrvProcessID, IsDummy) ' + @cr
insert into #ltrig (row) select @tab + 'values (@action, @stmnt, user_name(), getdate(), ''' + @fq_table + ''', @pk_names, @pk_old_values, @pk_new_values, @@NESTLEVEL, trigger_nestlevel(), @@SPID, 0)'+ @cr

insert into #ltrig (row) select 'end --WHILE' + @cr
insert into #ltrig (row) select 'DEALLOCATE ' + @newcur_name + @cr
insert into #ltrig (row) select 'DEALLOCATE ' + @oldcur_name + @cr

insert into #ltrig (row) select 'end' + @cr
insert into #ltrig (row) select 'go' + @cr

if (@DoDebug = 1) begin
	PRINT '/*****BEGIN DEBUG INFORMATION - Trigger cycle - INSERT *****'
	select * from #ltrig
	PRINT '/*****END DEBUG INFORMATION - Trigger cycle - INSERT *****'
end

select row from #ltrig order by id

return 0

end

go

--declare @stmnt_decl nvarchar(4000), @stmnt_body nvarchar(4000), 
--@stmnt_cycle_start nvarchar(4000), @stmnt_cycle_common nvarchar(4000), 
--@stmnt_cycle_common_text nvarchar(4000), 
--@stmnt_cycle_insert nvarchar(4000),
--@stmnt_cycle_insert_old nvarchar(4000),
--@stmnt_cycle_update nvarchar(4000), @stmnt_cycle_delete nvarchar(4000), @stmnt_cycle_end nvarchar(4000)

--exec up_GenerateSLTrigger 'LogTestText',  
--exec up_GenerateSLTrigger 'LogTestPK'
--exec up_GenerateSLTrigger 'DP_VR_Database'
--exec up_GenerateSLTrigger 'DP_VR_Detail'
--exec up_GenerateSLTrigger 'DP_VR_Header'
--exec up_GenerateSLTrigger 'DP_VR_Responses'
--,  
--@stmnt_decl OUT, @stmnt_body OUT, @stmnt_cycle_start OUT,
-- @stmnt_cycle_common OUT,  @stmnt_cycle_common_text OUT, @stmnt_cycle_insert OUT, @stmnt_cycle_insert_old OUT, 
--@stmnt_cycle_update OUT, @stmnt_cycle_delete OUT, @stmnt_cycle_end OUT
/*
PRINT @stmnt_decl
PRINT @stmnt_body 
PRINT @stmnt_cycle_start 
PRINT @stmnt_cycle_common 
PRINT @stmnt_cycle_common_text
PRINT @stmnt_cycle_insert
PRINT @stmnt_cycle_insert_old
PRINT @stmnt_cycle_update
PRINT @stmnt_cycle_delete
PRINT @stmnt_cycle_end
*/
--exec (@stmnt_decl + @stmnt_body + @stmnt_cycle_start + @stmnt_cycle_common + @stmnt_cycle_insert )
--select @stmnt 


--select * from #col_helper

